package so.sao.shop.supplier.util;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.NotOfficeXmlFileException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import so.sao.shop.supplier.config.CommConstant;
import so.sao.shop.supplier.config.Constant;
import so.sao.shop.supplier.pojo.Result;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by QuJunLong on 2017/7/12.
 */
public class ExcelReader {
    static private Workbook wb;
    static private Sheet sheet;
    static private Row row;
    private static DecimalFormat df = new DecimalFormat("0.##");             //数字格式，防止长数字成为科学计数法形式，或者int变为double形式
    /**
     * @param fileName ：Excel 文件路径
     * @return String[]
     * @method ：readExcelTitle<br>
     * @describe ：读取 Excel 文件<br>
     * @author ：wanglongjie<br>
     * @createDate ：2015年8月31日下午2:41:25 <br>
     */
    public static Result readExcelTitle(String fileName, int titleRow) {
        Result  result=getSheet(fileName);
        if(Constant.CodeConfig.CODE_FAILURE.equals(result.getCode()) ){
            return result;
        }
        sheet=(Sheet)result.getData();
        row = sheet.getRow(titleRow);// 获取第一行（约定第一行是标题行）
        int colNum = row.getPhysicalNumberOfCells();// 获取行的列数
        String[] titles = new String[colNum];
        for (int i = 0; i < titles.length; i++) {
            titles[i] = getCellFormatValue(row.getCell(i));
        }
        return Result.success("解析成功",titles);
    }
    /**
     * 根据Excel 的http路径下载Excel到目标路径下
     * @param excelUrl
     * @param tempPath
     * @param filename
     */
    public static void  downloadExcel(String  excelUrl,String  tempPath, String filename) throws IOException{
        try {
            FileUtils.copyInputStreamToFile(POIExcelUtil.getInputStream(excelUrl), new File(
                    tempPath, filename));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 判断Excel标题和模板Excel标题是否一致
     * @param excelTitles
     * @param templateTitles
     * @return
     */
    public static boolean  checkTitles(String [] excelTitles, String[] templateTitles) {
        List<String> list1 = Arrays.asList(excelTitles);
        boolean flg= true;
        for (String title : templateTitles) {
            if (!list1.contains(title)) {
                flg=false;
                break;
            }
        }
        return flg;
    }

    /**
     * @param fileName ：Excel 文件路径
     * @return List<Map<String,String>>
     * @method ：readExcelContent<br>
     * @describe ：读取 Excel 内容<br>
     * @author ：wanglongjie<br>
     * @createDate ：2015年8月31日下午3:12:06 <br>
     */
    public static Result  readExcelContent(String[] titles,String[] isNulltitles,String fileName, int contentStartRow) {
        Map<String, Object> maps = new HashMap<>();
        //Excel中正确记录信息
        Map<Integer, Map<String, String>> mapRight = new HashMap<>();
        //Excel中错误行号
        List<Map<String, Object>> errorRowList = new ArrayList<Map<String, Object>>();
        List<Integer> errornum=new ArrayList<Integer>();
        Map<String, String> content = null;
        Result  result=getSheet(fileName);
        if(Constant.CodeConfig.CODE_FAILURE.equals(result.getCode()) ){
            return result;
        }
       sheet=(Sheet)result.getData();
        // 得到总行数
        int rowNum = sheet.getLastRowNum();
        //和title一样
        row = sheet.getRow(contentStartRow - 1);
        int colNum = row.getPhysicalNumberOfCells();
        if(colNum !=titles.length){
            colNum=titles.length;
        }
        int succnum=0;
        // 正文内容应该从第二行开始,第一行为表头的标题
        for (int i = contentStartRow; i < rowNum + 1; i++) {
            int j = 0;
            row = sheet.getRow(i);
            content = new LinkedHashMap<>();
            if (row == null) {
                Map<String, Object> errorMap =new HashMap<String, Object>();
                errorMap.put("rowNum",i+1);
                errorMap.put("message","存在空单元格");
                errorRowList.add(errorMap);
                errornum.add(i+1);
                continue;
            }
            //获取表数据
            do {
                if (!(checkIsNull(isNulltitles,titles,j)) ) {
                    if ("".equals(getCellFormatValue(row.getCell(j)).trim())) {
                        Map<String, Object> errorMap =new HashMap<String, Object>();
                        errorMap.put("rowNum",i+1);
                        errorMap.put("message",titles[j]+"不能为空");
                        errorRowList.add(errorMap);
                        errornum.add(i+1);
                        content = null;
                        break;
                    }
                }
                content.put(titles[j], getCellFormatValue(row.getCell(j)).trim());
                j++;
            } while (j < colNum);
            if (content != null) {

                mapRight.put(i + 1, content);
            }
            succnum++;
        }
        // 正确excel数据
        maps.put("mapright", mapRight);
        // 错误数据
        maps.put("maperror", errorRowList);
        // 总条数
        maps.put("totalnum", rowNum);
        // 成功条数
        maps.put("succnum", succnum);
        // 失败条数
        maps.put("failnum", rowNum-succnum);
        // 错误行号
        maps.put("errorrowno", errornum);
        return Result.success("解析成功",maps);
    }

    /**
     * 根据文件名称分版本获取Sheet对象
     * @param fileName
     * @return
     */
    private  static Result getSheet(String fileName){

        InputStream is;
        try {
            is = new FileInputStream(fileName);
            String postfix = fileName.substring(fileName.lastIndexOf("."), fileName.length());
            if (postfix.equals(".xls")) {
                // 针对 2003 Excel 文件
                wb = new HSSFWorkbook(new POIFSFileSystem(is));
                sheet = wb.getSheetAt(0);
            } else {
                // 针对2007 Excel 文件
                wb = new XSSFWorkbook(is);
                sheet = wb.getSheetAt(0);
            }
        } catch (IOException e) {
           return   Result.fail("解析失败");
        }catch (NotOfficeXmlFileException ex){
            return   Result.fail("解析失败");
        }
        return Result.success("解析成功",sheet);
    }

    /**
     * 检查标题可以为空
     * @param isNulltitles
     * @param titles
     * @param j
     * @return
     */
    public static boolean  checkIsNull(String[] isNulltitles,String[] titles,int j){
        boolean  flg=false;
        for (String title:isNulltitles){
            flg=title.equals(titles[j]) ;
            if(flg){
                break;
            }
        }
        return  flg;
    }

    /**
     * 根据Cell类型设置数据
     * @param cell
     * @return
     */
    private static String getCellFormatValue(Cell cell) {
        String cellvalue = "";
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellType()) {
                // 如果当前Cell的Type为NUMERIC
                case Cell.CELL_TYPE_NUMERIC:
                    // 如果是纯数字取得当前Cell的数值
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        //  如果是date类型则 ，获取该cell的date值
                        Date date = cell.getDateCellValue();
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        cellvalue = sdf.format(date);
                    } else { // 纯数字
                        cellvalue = df.format(cell.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_FORMULA: {
                    // 判断当前的cell是否为Date
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        // 方法2：这样子的data格式是不带带时分秒的：2011-10-12
                        Date date = cell.getDateCellValue();
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        cellvalue = sdf.format(date);
                    } else {
                        // 如果是纯数字取得当前Cell的数值
                        cellvalue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                // 如果当前Cell的Type为STRIN
                case Cell.CELL_TYPE_STRING:
                    // 取得当前的Cell字符串
                    cellvalue = cell.getStringCellValue();
                    break;
                default:
                    // 默认的Cell值
                    cellvalue = cell.getRichStringCellValue() == null ? "" : cell.getRichStringCellValue().toString();
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;
    }


}
